import 'package:sqlite3/sqlite3.dart';
import '../model/model_language.dart';
import '../model/model_user_album_media_record.dart';
import '../tools/tools_database.dart';

class DaoUserAlbumMediaRecord {
  static int insert(ModelUserAlbumMediaRecord l) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
    INSERT INTO 
      p001_user_album_media_record(
      album_id,media_id,sentence_id,sentence,start_time,end_time,
      status,start_review_time,review_progress,deferred,
      right_qty,wrong_qty,
      gmt_create,gmt_modified,remark)
    VALUES
      (?,?,?,?,?,?,
      ?,?,?,?,
      ?,?,
      ?,?,?)
    ;
    ''';
    final stmt = db.prepare(sql);
    DateTime now = DateTime.now();
    int gmt_create = now.millisecondsSinceEpoch;
    stmt
      ..execute([
        l.albumId,
        l.mediaId,
        l.sentenceId,
        l.sentence,
        l.startTime,
        l.endTime,
        l.status,
        l.startReviewTime,
        l.reviewProgress,
        l.deferred,
        l.rightQty,
        l.wrongQty,
        gmt_create,
        gmt_create,
        l.remark
      ]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static int deleteById(int id) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      delete from 
        p001_user_album_media_record 
      where 
        id = ?;
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([id]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static int updateById(ModelUserAlbumMediaRecord l) {
    print(l);
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      update 
        p001_user_album_media_record
      set
        album_id = ?,media_id = ?,sentence_id = ?,sentence = ?,start_time = ?,end_time = ?,
        status = ?,start_review_time = ?,review_progress = ?,last_review_time = ?, deferred = ?,
        right_qty = ?,wrong_qty = ?,
        gmt_modified = ?,
        remark = ?
      where
        id = ?
      ;
    ''';

    final stmt = db.prepare(sql);
    try {
      int gmt_modified = DateTime.now().millisecondsSinceEpoch;
      stmt
        ..execute([
          l.albumId,
          l.mediaId,
          l.sentenceId,
          l.sentence,
          l.startTime,
          l.endTime,
          l.status,
          l.startReviewTime,
          l.reviewProgress,
          l.lastReviewTime,
          l.deferred,
          l.rightQty,
          l.wrongQty,
          gmt_modified,
          l.remark,
          l.id
        ]);
      print("执行成功");
    } catch (e) {
      print("出现异常");
      print(e);
    }

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static List<ModelUserAlbumMediaRecord> findAll() {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album_media_record
      ;
    ''';
    ResultSet rs = db.select(sql);
    Iterator<Row> i = rs.iterator;
    List<ModelUserAlbumMediaRecord> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelUserAlbumMediaRecord w = new ModelUserAlbumMediaRecord();
      w.id = r["id"];
      w.albumId = r["album_id"];
      w.mediaId = r["media_id"];
      w.sentenceId = r["sentence_id"];
      w.sentence = r["sentence"];
      w.startTime = r["start_time"];
      w.endTime = r["end_time"];
      w.status = r["status"];
      w.startReviewTime = r["start_revice_time"];
      w.reviewProgress = r["revie_progress"];
      w.lastReviewTime = r["last_review_time"];
      w.deferred = r["deferred"];
      w.rightQty = r["right_qty"];
      w.wrongQty = r["wrong_qty"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];

      list.add(w);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static List<ModelUserAlbumMediaRecord> findAllByMediaId(int albumId, int mediaId) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album_media_record
      where
        album_id = ?
      and
        media_id = ?
      ;
    ''';
    ResultSet rs = db.select(sql, [albumId, mediaId]);
    Iterator<Row> i = rs.iterator;
    List<ModelUserAlbumMediaRecord> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelUserAlbumMediaRecord w = new ModelUserAlbumMediaRecord();
      w.id = r["id"];
      w.albumId = r["album_id"];
      w.mediaId = r["media_id"];
      w.sentenceId = r["sentence_id"];
      w.sentence = r["sentence"];
      w.startTime = r["start_time"];
      w.endTime = r["end_time"];
      w.status = r["status"];
      w.startReviewTime = r["start_review_time"];
      w.reviewProgress = r["review_progress"];
      w.lastReviewTime = r["last_review_time"];
      w.deferred = r["deferred"];
      w.rightQty = r["right_qty"];
      w.wrongQty = r["wrong_qty"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];

      list.add(w);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static ModelUserAlbumMediaRecord? findById(int id) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album_media_record
      where
        id = ?
      ;
    ''';
    ModelUserAlbumMediaRecord? w = null;
    ResultSet rs = db.select(sql, [id]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      w = new ModelUserAlbumMediaRecord();
      w.id = r["id"];
      w.albumId = r["album_id"];
      w.mediaId = r["media_id"];
      w.sentenceId = r["sentence_id"];
      w.sentence = r["sentence"];
      w.startTime = r["start_time"];
      w.endTime = r["end_time"];
      w.status = r["status"];
      w.startReviewTime = r["start_revice_time"];
      w.reviewProgress = r["review_progress"];
      w.lastReviewTime = r["last_review_time"];
      w.deferred = r["deferred"];
      w.rightQty = r["right_qty"];
      w.wrongQty = r["wrong_qty"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return w;
  }

  static ModelUserAlbumMediaRecord? findByAlbumAndMediaAndSentence(int? albumId, int? mediaId, int? sentenceId) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album_media_record
      where
        album_id = ?
      and 
        media_id = ?
      and
        sentence_id = ?
      ;
    ''';
    ResultSet rs = db.select(sql, [albumId, mediaId, sentenceId]);
    Iterator<Row> i = rs.iterator;
    ModelUserAlbumMediaRecord? w = null;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      w = new ModelUserAlbumMediaRecord();
      w.id = r["id"];
      w.albumId = r["album_id"];
      w.mediaId = r["media_id"];
      w.sentenceId = r["sentence_id"];
      w.sentence = r["sentence"];
      w.startTime = r["start_time"];
      w.endTime = r["end_time"];
      w.status = r["status"];
      w.startReviewTime = r["start_review_time"];
      w.reviewProgress = r["review_progress"];
      w.lastReviewTime = r["last_review_time"];
      w.deferred = r["deferred"];
      w.rightQty = r["right_qty"];
      w.wrongQty = r["wrong_qty"];

      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return w;
  }

  static void deleteByAlbumId(int id) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      delete from 
        p001_user_album_media_record 
      where 
        album_id = ?
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([id]);

    // 3. 关闭连接
    db.dispose();
  }
}
